package org.bigjava.travel.provider;

import org.springframework.stereotype.Component;

/**
 * 景点的sql语句
 */

@Component
public class ScenicSpotSqlProvider {

    //查询所有的信息
    public String count(){ return "SELECT count(*) FROM t_scenic_spot WHERE DELETE_STATUS=0"; }

    public String count2(){ return "SELECT count(*) FROM t_scenic_spot WHERE DELETE_STATUS=0 AND STATE=1"; }

    //通过ID查询景点
    public String findById() {
        return "SELECT * FROM t_scenic_spot WHERE ID = #{id} AND DELETE_STATUS=0 ";
    }

    //查询所有景点并由ID排序
    public String findList(){
        return "SELECT * FROM t_scenic_spot WHERE DELETE_STATUS=0 ORDER BY ID ASC";
    }

    //查询所有已发布且未撤销的景点
    public String indexList(){
        return "SELECT * FROM t_scenic_spot WHERE DELETE_STATUS=0 AND STATE=1 ORDER BY ID ASC";
    }

    //通过景点名称模糊查询
    public String findListByQuery(){
        return "SELECT * FROM t_scenic_spot WHERE DELETE_STATUS=0 AND SPOT_NAME LIKE #{query,jdbcType=VARCHAR} ORDER BY ID ASC";
    }

    //修改景点信息
    public String update(){
        return "UPDATE t_scenic_spot SET SPOT_NAME=#{spotName},SPOT_INTRO=#{spotIntro},SPOT_STAR=#{spotStar}," +
                "SPOT_ADDRESS=#{spotAddress},OPEN_TIME=#{openTime},TICKETS_MESSAGE=#{ticketsMessage},STATE=#{state},IMG_URL=#{imgUrl}" +
                " WHERE ID=#{id}";
    }

    //添加景点
    public String save(){
        return "INSERT INTO t_scenic_spot(SPOT_NAME,SPOT_INTRO,SPOT_STAR,SPOT_ADDRESS,OPEN_TIME,TICKETS_MESSAGE,STATE,IMG_URL) " +
                "VALUES(#{spotName},#{spotIntro},#{spotStar},#{spotAddress},#{openTime},#{ticketsMessage},#{state},#{imgUrl})";
    }

    //通过ID删除景点
    public String deleteById(){
        return "UPDATE t_scenic_spot SET DELETE_STATUS=1 WHERE id=#{id}";
    }


    public String state0count(){ return "SELECT count(*) FROM t_scenic_spot WHERE DELETE_STATUS=0 AND STATE=0"; }

    public String state1count(){ return "SELECT count(*) FROM t_scenic_spot WHERE DELETE_STATUS=0 AND STATE=1"; }

    public String state2count(){ return "SELECT count(*) FROM t_scenic_spot WHERE DELETE_STATUS=0 AND STATE=2"; }

}
